package com.tzinfo.tzt.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.tzinfo.tzt.config.AppDataConfig;

/**
 * @author yxw
 * 
 */
public class DBHelper extends SQLiteOpenHelper {
	// 数据库名称常量
	public final static String DB_NAME = "tzinfo";
	// 数据库版本常量
	public final static int VERSION = 1;

	private static DBHelper instance = null;

	private SQLiteDatabase database;

	public SQLiteDatabase getDatabase() {
		return database;
	}

	public void setDatabase(SQLiteDatabase database) {
		this.database = database;
	}

	public static DBHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DBHelper(context);
		}
		instance.openDatabase();
		return instance;
	}

	public void openDatabase() {
		if (database == null) {
			database = this.getWritableDatabase();
		}
		onCreate(database);
	}

	/**
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public DBHelper(Context context) {
		super(context, DB_NAME, null, VERSION);
	}
	
	public void createTuanTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 新房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.TuanTables + " ( ")
				.append("_id integer primary key , ").append("pics text,")
				.append("name text,").append("sales text,")
				.append("caption text,").append("isGroup int,")
				.append("signCount text,").append("remainTime text,")
				.append("introduce text,").append("notice text,")
				.append("time timestamp)");
		Log.e("sqlString", sqlString.toString());
		db.execSQL(sqlString.toString());
	}
	
	//创建看房单
	public void createSeeNoteTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 新房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.SeeNote + " ( ")
				.append("_id integer primary key , ").append("houseid integer , ")
				.append("villageName text,").append("villageAddress text,")
				.append("price text,").append("type text,")
				.append("size integer,").append("floor text,")
				.append("direction text,").append("remark text,")
				.append("agentName text,").append("agentTel text,")
				.append("date text,").append("time text,")
				.append("images text,")
				.append("lastModifyTime timestamp)");
		Log.e("sqlString", sqlString.toString());
		db.execSQL(sqlString.toString());
	}
	
	
	//创建看房单
		public void createCommCollectionTable(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			StringBuffer sqlString = new StringBuffer();
			// 新房table
			sqlString
					.append("create table if not exists "
							+ AppDataConfig.CollectionTables[2] + " ( ")
					.append("_id integer primary key , ")
					.append("name text,").append("address text,")
					.append("pics text,").append("averagePrice text,")
					.append("priceIncrease double,").append("houseCount text,")
					.append("time timestamp)");
			Log.e("sqlString", sqlString.toString());
			db.execSQL(sqlString.toString());
		}
		

	public void createNewHistoryTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 新房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.BrowsingHistoryTables[1] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("status text,").append("attr integer,")
				.append("price integer,").append("priceUnit text,")
				.append("lon double,").append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}
	public void createNewCallTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 新房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CallHistoryTables[1] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("status text,").append("attr integer,")
				.append("price integer,").append("priceUnit text,")
				.append("lon double,").append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}
	public void createNewCollectionTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 新房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CollectionTables[1] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("status text,").append("attr integer,")
				.append("price integer,").append("priceUnit text,")
				.append("lon double,").append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}

	public void createRentHistoryTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 租房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.BrowsingHistoryTables[2] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("price text,")
				.append("priceUnit text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}
	public void createRentCallTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 租房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CallHistoryTables[2] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("price text,")
				.append("priceUnit text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}
	public void createRentCollectionTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		StringBuffer sqlString = new StringBuffer();
		// 租房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CollectionTables[3] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("price text,")
				.append("priceUnit text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		db.execSQL(sqlString.toString());
	}

	public void createOldHistoryTable(SQLiteDatabase db) {
		StringBuffer sqlString = new StringBuffer();
		// 二手房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.BrowsingHistoryTables[0] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("size text,")
				.append("price text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		Log.e("sql", sqlString.toString());
		db.execSQL(sqlString.toString());
	}
	public void createOldCallTable(SQLiteDatabase db) {
		StringBuffer sqlString = new StringBuffer();
		// 二手房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CallHistoryTables[0] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("size text,")
				.append("price text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		Log.e("sql", sqlString.toString());
		db.execSQL(sqlString.toString());
	}
	public void createOldCollectionTable(SQLiteDatabase db) {
		StringBuffer sqlString = new StringBuffer();
		// 二手房table
		sqlString
				.append("create table if not exists "
						+ AppDataConfig.CollectionTables[0] + " ( ")
				.append("_id integer primary key , ").append("name text,")
				.append("address text,").append("logo text,")
				.append("style text,").append("size text,")
				.append("price text,").append("lon double,")
				.append("lat double,")
				.append("time timestamp)");
		Log.e("sql", sqlString.toString());
		db.execSQL(sqlString.toString());
	}

	// 创建时调用
	@Override
	public void onCreate(SQLiteDatabase db) {
		createTuanTable(db);
		createSeeNoteTable(db);
		createCommCollectionTable(db);
		
		createOldHistoryTable(db);
		createOldCallTable(db);
		createOldCollectionTable(db);
		
		createNewHistoryTable(db);
		createNewCallTable(db);
		createNewCollectionTable(db);
		
		createRentHistoryTable(db);
		createRentCallTable(db);
		createRentCollectionTable(db);
	}

	// 版本更新时调用
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
//		arg0.execSQL("drop table if exists "
//				+ AppDataConfig.BrowsingHistoryTables[0]);
//		arg0.execSQL("drop table if exists "
//				+ AppDataConfig.BrowsingHistoryTables[1]);
//		arg0.execSQL("drop table if exists "
//				+ AppDataConfig.BrowsingHistoryTables[2]);
//		onCreate(database);
	}

	public Cursor query(String table) {
		return database.rawQuery("select * from " + table, null);
	}
	public Cursor query(String table,String order,String sx) {
		return database.rawQuery("select * from " + table+" order by "+order+" "+sx, null);
	}
	public boolean deleteAll(String table) {
		String sql="drop table if exists "+table;
		database.execSQL(sql);
		onCreate(database);
		return true;
	}
	public int deleteById(String table,String id) {
		return database.delete(table, "_id=?", new String[]{id});
	}

	public Cursor query(String table, String id) {
		return database.rawQuery("select * from " + table + " where _id=?",
				new String[] { id });
	}

	public long save(String table, ContentValues value) {
		if (value.get("_id")!=null&&this.query(table, String.valueOf(value.get("_id"))).getCount() > 0) {
			return database.update(table, value, "_id=?",
					new String[] { String.valueOf(value.get("_id")) });
		} else {
			return database.insert(table, null, value);
		}
	}
}
